﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DaiKin.DBDataAccessLib.Helper;
using System.Data;
using System.Data.OracleClient;
using System.Data.SqlClient;
using DBDataAccessLib.DataMapping;
using System.Data.Common;

namespace DBDataAccessLib
{
    public abstract class MachineInfoDataAccess
    {

        public static int AddMachineInfo(string WCNO, string stationName, string model, string sebango, int valid, string essentials, string cycleTime,
            string setUser, string setDate)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.AddMachineInfo,
                new OracleParameter(":wcno", WCNO),
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":model", model),
                new OracleParameter(":sebango", sebango),
                new OracleParameter(":valid", valid),
                new OracleParameter(":essentials", essentials),
                new OracleParameter(":cycleTime", cycleTime),
                new OracleParameter(":setUser", setUser),
                new OracleParameter(":setDate", setDate)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.AddMachineInfo,
                    new SqlParameter("@wcno", WCNO),
                    new SqlParameter("@stationName", stationName),
                    new SqlParameter("@model", model),
                    new SqlParameter("@sebango", sebango),
                    new SqlParameter("@valid", valid),
                    new SqlParameter("@essentials", essentials),
                    new SqlParameter("@cycleTime", cycleTime),
                    new SqlParameter("@setUser", setUser),
                    new SqlParameter("@setDate", setDate)
                    );
            return affectRows;
        }

        public static MachineInfoRecordMapping GetMachineInfoByPrimKey(string WCNO, string stationName, string model, int valid)
        {
            MachineInfoRecordMapping info = null;
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectRecordByKeyAndValid,
                    new OracleParameter(":wcno", WCNO),
                    new OracleParameter(":stationName", stationName),
                    new OracleParameter(":model", model),
                    new OracleParameter(":valid", valid)
                    );
            else
                reader = SqlHelper.ExecuteReader(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.SelectRecordByKeyAndValid,
                new SqlParameter("@wcno", WCNO),
                new SqlParameter("@stationName", stationName),
                new SqlParameter("@model", model),
                new SqlParameter("@valid", valid)
                );
            while (reader.Read())
            {
                info = new MachineInfoRecordMapping();
                info.WCNO = reader.GetString(0).Trim();
                info.StationName = reader.GetString(1).Trim();
                info.Model = reader.GetString(2).Trim();
                info.Sebango = reader.GetString(3).Trim();
                info.Essentials = reader.GetString(4).Trim();
                info.CycleTime = reader.GetString(5).Trim();
                info.Valid = reader.GetInt32(6);
                info.SetId = reader.GetString(7).Trim();
                info.SetDate = reader.GetString(8).Trim();
                info.ApplyId = (reader.GetValue(9) == DBNull.Value) ? "" : reader.GetString(9).Trim();
                info.ApplyDate = (reader.GetValue(10) == DBNull.Value) ? "" : reader.GetString(10).Trim();
                break;
            }
            return info;
        }

        public static List<MachineInfoRecordMapping> GetMachineInfoByConditions(string wcno, string stationName, string model,
            string sebango, AccountMapping userName)
        {
            List<MachineInfoRecordMapping> mInfo = new List<MachineInfoRecordMapping>();
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectMachineInfoByConditions,
                    new OracleParameter(":wcno", "%" + wcno + "%"),
                    new OracleParameter(":stationName", "%" + stationName + "%"),
                    new OracleParameter(":model", "%" + model + "%"),
                    new OracleParameter(":sebango", "%" + sebango + "%"),
                    new OracleParameter(":setId", "%" + userName.UserName + "%")
                    );
            else
                reader = SqlHelper.ExecuteReader(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectMachineInfoByConditions,
                    new SqlParameter("@wcno", "%" + wcno + "%"),
                    new SqlParameter("@stationName", "%" + stationName + "%"),
                    new SqlParameter("@model", "%" + model + "%"),
                    new SqlParameter("@sebango", "%" + sebango + "%"),
                    new SqlParameter("@setId", "%" + userName.UserName + "%")
                    );
            while (reader.Read())
            {
                MachineInfoRecordMapping info = new MachineInfoRecordMapping();
                info.WCNO = reader.GetString(0).Trim();
                info.StationName = reader.GetString(1).Trim();
                info.Model = reader.GetString(2).Trim();
                info.Sebango = reader.GetString(3).Trim();
                info.Essentials = reader.GetString(4).Trim();
                info.CycleTime = reader.GetString(5).Trim();
                info.Valid = reader.GetInt32(6);
                info.SetId = reader.GetString(7).Trim();
                info.SetDate = reader.GetString(8).Trim();
                info.ApplyId = (reader.GetValue(9) == DBNull.Value) ? "" : reader.GetString(9).Trim();
                info.ApplyDate = (reader.GetValue(10) == DBNull.Value) ? "" : reader.GetString(10).Trim();
                mInfo.Add(info);
            }
            return mInfo;
        }

        public static int UpdateMachineInfo(string WCNO, string stationName, string model, string sebango, string essentials, string cycleTime,
            string setUser, string setDate)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.UpdateMachineInfo,
                new OracleParameter(":wcno", WCNO),
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":model", model),
                new OracleParameter(":sebango", sebango),
                new OracleParameter(":essentials", essentials),
                new OracleParameter(":cycleTime", cycleTime),
                new OracleParameter(":setUser", setUser),
                new OracleParameter(":setDate", setDate)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                    SqlHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.UpdateMachineInfo,
                    new SqlParameter("@wcno", WCNO),
                    new SqlParameter("@stationName", stationName),
                    new SqlParameter("@model", model),
                    new SqlParameter("@sebango", sebango),
                    new SqlParameter("@essentials", essentials),
                    new SqlParameter("@cycleTime", cycleTime),
                    new SqlParameter("@setUser", setUser),
                    new SqlParameter("@setDate", setDate)
                    );
            return affectRows;
        }

        public static int DeleteMachineInfo(string WCNO, string stationName, string model)
        {
            int affectRows;
            if (DBHelperFactory.DbName == DBType.Oracle)
                affectRows = OracleHelper.ExecuteNonQuery(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteMachineInfo,
                new OracleParameter(":wcno", WCNO),
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":model", model)
                );
            else
                affectRows = SqlHelper.ExecuteNonQuery(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.DeleteMachineInfo,
                new SqlParameter("@wcno", WCNO),
                new SqlParameter("@stationName", stationName),
                new SqlParameter("@model", model)
                );
            return affectRows;
        }

        public static string ApplyMachineInfoByPrimKey(string wcno, string stationName, string model, string applyUser, string applyDate)
        {
            bool needUpdateValid1ToValid2 = false;
            object checkValidIs1Existed;
            int maxValid = 1;
            if (DBHelperFactory.DbName == DBType.Oracle)
                checkValidIs1Existed = OracleHelper.ExecuteScalar(
                OracleHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.SelectRecordByKeyAndValid,
                new OracleParameter(":wcno", wcno),
                new OracleParameter(":stationName", stationName),
                new OracleParameter(":model", model),
                new OracleParameter(":valid", 1)
                );
            else
                checkValidIs1Existed = SqlHelper.ExecuteScalar(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.SelectRecordByKeyAndValid,
                new SqlParameter("@wcno", wcno),
                new SqlParameter("@stationName", stationName),
                new SqlParameter("@model", model),
                new SqlParameter("@valid", 1)
                );
            if (checkValidIs1Existed != null)
            {
                needUpdateValid1ToValid2 = true;
                List<MachineInfoRecordMapping> records = GetMachineInfoByKey(wcno, stationName, model);
                foreach (var item in records)
                {
                    if (item.Valid > maxValid) maxValid = item.Valid;
                }
                maxValid++;
            }
            if (DBHelperFactory.DbName == DBType.Oracle)
                using (OracleConnection cn = new OracleConnection(OracleHelper.ConnectionStringLocalTransaction))
                {
                    cn.Open();
                    using (OracleTransaction trans = cn.BeginTransaction())
                    {
                        try
                        {
                            if (needUpdateValid1ToValid2)
                            {
                                int findValid = 1;
                                OracleHelper.ExecuteNonQuery(trans, CommandType.Text, SqlProvider.UpdateValidByKeyAndValidWithoutApplyID,
                                    new OracleParameter(":wcno", wcno),
                                    new OracleParameter(":stationName", stationName),
                                    new OracleParameter(":model", model),
                                    new OracleParameter(":setValid", maxValid),
                                    new OracleParameter(":findValid", findValid)
                                    );
                            }
                            int setValid2 = 1, findValid2 = 0;
                            OracleHelper.ExecuteNonQuery(trans, CommandType.Text, SqlProvider.UpdateValidByKeyAndValid,
                                    new OracleParameter(":wcno", wcno),
                                    new OracleParameter(":stationName", stationName),
                                    new OracleParameter(":model", model),
                                    new OracleParameter(":setValid", setValid2),
                                    new OracleParameter(":findValid", findValid2),
                                    new OracleParameter(":applyUser", applyUser),
                                    new OracleParameter(":applyDate", applyDate));
                            trans.Commit();
                            return null;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            return ex.Message;
                        }
                    }
                }
            else
                using (SqlConnection cn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction))
                {
                    cn.Open();
                    using (SqlTransaction trans = cn.BeginTransaction())
                    {
                        try
                        {
                            if (needUpdateValid1ToValid2)
                            {
                                int findValid = 1;
                                SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SqlProvider.UpdateValidByKeyAndValidWithoutApplyID,
                                    new SqlParameter("@wcno", wcno),
                                    new SqlParameter("@stationName", stationName),
                                    new SqlParameter("@model", model),
                                    new SqlParameter("@setValid", maxValid),
                                    new SqlParameter("@findValid", findValid));
                            }
                            int setValid2 = 1, findValid2 = 0;
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, SqlProvider.UpdateValidByKeyAndValid,
                                    new SqlParameter("@wcno", wcno),
                                    new SqlParameter("@stationName", stationName),
                                    new SqlParameter("@model", model),
                                    new SqlParameter("@setValid", setValid2),
                                    new SqlParameter("@findValid", findValid2),
                                    new SqlParameter("@applyUser", applyUser),
                                    new SqlParameter("@applyDate", applyDate));
                            trans.Commit();
                            return null;
                        }
                        catch (Exception ex)
                        {
                            trans.Rollback();
                            return ex.Message;
                        }
                    }
                }
        }

        private static List<MachineInfoRecordMapping> GetMachineInfoByKey(string wcno, string stationName, string model)
        {
            List<MachineInfoRecordMapping> records = new List<MachineInfoRecordMapping>();
            DbDataReader reader;
            if (DBHelperFactory.DbName == DBType.Oracle)
                reader = OracleHelper.ExecuteReader(
                    OracleHelper.ConnectionStringLocalTransaction,
                    CommandType.Text,
                    SqlProvider.SelectMachineInfo,
                    new OracleParameter(":wcno", wcno),
                    new OracleParameter(":stationName", stationName),
                    new OracleParameter(":model", model)
                    );
            else
                reader = SqlHelper.ExecuteReader(
                SqlHelper.ConnectionStringLocalTransaction,
                CommandType.Text,
                SqlProvider.SelectMachineInfo,
                new SqlParameter("@wcno", wcno),
                new SqlParameter("@stationName", stationName),
                new SqlParameter("@model", model)
                );
            while (reader.Read())
            {
                MachineInfoRecordMapping info = new MachineInfoRecordMapping();
                info.WCNO = reader.GetString(0).Trim();
                info.StationName = reader.GetString(1).Trim();
                info.Model = reader.GetString(2).Trim();
                info.Sebango = reader.GetString(3).Trim();
                info.Essentials = reader.GetString(4).Trim();
                info.CycleTime = reader.GetString(5).Trim();
                info.Valid = reader.GetInt32(6);
                info.SetId = reader.GetString(7).Trim();
                info.SetDate = reader.GetString(8).Trim();
                info.ApplyId = (reader.GetValue(9) == DBNull.Value) ? "" : reader.GetString(9).Trim();
                info.ApplyDate = (reader.GetValue(10) == DBNull.Value) ? "" : reader.GetString(10).Trim();
                records.Add(info);
            }
            return records;
        }
    }
}
